﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Configuration; // 读取配置文件的引用
using System.Data;
using System.Data.SqlClient; // 访问sqlserver需要的命名空间

namespace ZJF.DAL
{
    /// <summary>
    /// 通用数据访问类
    /// </summary>
    internal class SQLHelper
    {
        // 读取链接字符串
        private static string connString = ConfigurationManager.ConnectionStrings["connString1"].ToString();

        /// <summary>
        /// 增加、删除、修改操作，返回被影响的行数
        /// </summary>
        /// <param name="cmdText"></param> sql语句
        /// <param name="paramArray"></param> sql语句的参数
        /// <returns></returns>
        public static int ExecuteNonQuery(string cmdText, SqlParameter[] paramArray = null) 
        {
            // 创建链接对象，使用只有一个链接字符串的构造方法
            SqlConnection conn = new SqlConnection(connString);
            // 创建Command对象，传入sql和链接对象
            SqlCommand cmd =new SqlCommand(cmdText, conn);
            // 判断是否有参数传递
            if (paramArray != null) { 
                cmd.Parameters.AddRange(paramArray);
            }
            try
            {
                // 打开链接
                conn.Open();
                // 没有错误直接返回
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // 可以写入日志
                string errMsg = $"{DateTime.Now}:执行xxx发生异常{ex.Message}";
                throw new Exception(errMsg);
            }
            finally {
                conn.Close(); 
            }   
        }

        /// <summary>
        /// 返回一个查询结果
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="paramArray"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static object ExecuteScalar(string cmdText, SqlParameter[] paramArray = null)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (paramArray != null) { 
                cmd.Parameters.AddRange (paramArray);
            }
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw new Exception($"执行ExecuteScalar失败{ex.Message}");
            }
            finally {
                conn.Close();
            }
        }

        /// <summary>
        /// 返回结果集
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="paramArray"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static SqlDataReader ExecuteReader(string cmdText, SqlParameter[] paramArray = null)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (paramArray != null)
            {
                cmd.Parameters.AddRange(paramArray);
            }
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            { 
                throw new Exception($"执行ExecuteReader失败{ex.Message}");
            }
        }
        
        /// <summary>
        /// 返回一个DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataSet GetDataSet(string sql, string tableName = null)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                if (tableName == null) {
                    da.Fill(ds);
                }
                else {
                    da.Fill(ds, tableName);
                }
                return ds;
            }
            catch (Exception ex)
            {

                throw new Exception($"执行GetDataSet出错{ex.Message}");
            }
            finally { conn.Close(); }   
        }

        /// <summary>
        /// 返回多个数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataSet GetDataSet(Dictionary<string, string> dictTableAndSql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection= conn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                foreach (string tbName  in dictTableAndSql.Keys)
                {
                    cmd.CommandText = dictTableAndSql[tbName];
                    da.Fill(ds, tbName);
                }
                return ds;
            }
            catch (Exception ex)
            {

                throw new Exception($"执行GetDataSet出错{ex.Message}");
            }
            finally { conn.Close(); }
        }
    }
}
